﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YNCNumberSys.db;

namespace YNCNumberSys.DbDal
{
    /// <summary>
    /// 产品信息
    /// </summary>
    public class ProductInfoDal
    {
        private CommandManager commandManager = new CommandManager();
        private ConnectionManager connectionManager = new ConnectionManager();
        public DataTable GetProductInfoDatable()
        {
            string sql = string.Format(@"SELECT [ID]
                                              ,[机床型号]
                                              ,[A号]
                                          FROM [YNCNumberSys].[dbo].[产品信息]; ");
            return commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql, "产品信息");
        }
        /// <summary>
        /// 更新到产品数据库中。
        /// </summary>
        /// <param name="jcxh"></param>
        /// <param name="aH"></param>
        /// <returns></returns>
        public bool UpdateProductInfoDatable(string jcxh,string aH)
        {
            string insertSql = string.Format(
               @"INSERT INTO [YNCNumberSys].[dbo].[产品信息]
                           ([机床型号]
                           ,[A号])
                     VALUES
                           ('{0}'
                           ,'{1}'); "
                           , jcxh
                           , aH);
            string updateSql = string.Format(
               @"UPDATE [YNCNumberSys].[dbo].[产品信息]
                       SET [机床型号] = '{0}'
                          ,[A号] = '{1}' "
                           , jcxh
                           , aH);
            string selectSQL = string.Empty;
            selectSQL = string.Format(@"if NOT exists(SELECT Top 1 * FROM [YNCNumberSys].[dbo].[产品信息] where [机床型号]='{0}' and [A号]='{1}') ", jcxh,aH);
            string strSql = selectSQL + " " + insertSql + "ELSE " + updateSql + ";";
            int row = commandManager.ExecuteScalar(connectionManager.getConnection(), strSql);
            return row == 1 ? true : false;
        }
    }
}
